from urllib.parse import quote

import xlwt
from django.db.models import Count, Sum
from django.http import JsonResponse, HttpResponse
from django.shortcuts import render

from app01.models import 报支工程
from app01.utils.fieldshtml import fields_to_html, fields_to_save
from app01.utils.pagination import Pagination


def contract_old_list(request):
    contract_old_list_all = 报支工程.objects.all().order_by('-发票已送', '结算月份')
    # 生成是否表格字段选择框HTML代码“select_str”，是否显示字段的配置“dicts”
    select_str, dicts = fields_to_html(request.session['info']['employeeid'], 报支工程)

    # 生成分布HTML代码
    page = Pagination(request, contract_old_list_all)

    # 生成表格底部的合计栏
    templist = contract_old_list_all[page.start_recno:page.end_recno]
    # print(templist.values())
    sum_row = templist.aggregate(Count('项目名称'), Sum('结算金额'), Sum('奖励金额'), Sum('考核扣款'),
                                 Sum('能源费'), Sum('bggyjhba'), Sum('bggyjhca'), Sum('bggyjhcb'), Sum('bggyjhcc'),
                                 Sum('bggyjhce'), Sum('bggyjhcf1'), Sum('bggyjhcf2'), Sum('bggyjhcg'), Sum('bggyjhch'),
                                 Sum('bggyjhcj'), Sum('bggyjhcs'), Sum('bggyjhcm'), Sum('bggyjhcn'), Sum('bggyjhfa'),
                                 Sum('bggyjhfb'), Sum('bggyjhfc'), Sum('bggyjhfj'), Sum('bggyjhfn'), Sum('bggyjhfp'),
                                 Sum('bggyjhga'), Sum('bggyjhgb'), Sum('bggyjhia'), Sum('厂部及其他'))
    # 将数字转换成千分位表示
    for key, value in sum_row.items():
        if (value == 0) or (value is None):
            sum_row[key] = '-'
        else:
            sum_row[key] = '{:,}'.format(value)

    context = {
        'sum_row': sum_row,
        'fields': dicts,
        'select': select_str,
        'data_list': page.data_list,  # 要显示的数据记录
        'page_string': page.html(),  # 分页的HTML代码
        'start_recno': page.start_recno,
        'end_recno': page.end_recno,
        'active3': 'active',
        'title': '产销资讯02系统合同查询'
    }
    return render(request, 'contract_old_list.html', context)


def contract_old_fields(request):
    fields_str = request.GET.get('f')
    sel_fields = fields_str.split(',')
    # print(type(sel_fields))
    fields_to_save(request.session['info']['employeeid'], 报支工程, sel_fields)
    return JsonResponse({'status': True})

def export_excel_old(request):
    # 开始记录
    start_recno = int(request.GET.get('start_recno', 0))
    # 结束记录
    end_recno = int(request.GET.get('end_recno', 10))

    filename = quote('产销资讯02系统合同.xls')
    response = HttpResponse(content_type='application/vnd.msexecl')
    response['Content-Disposition'] = f'attachment; filename="{filename}"'

    data_list = 报支工程.objects.all().order_by('-发票已送', '结算月份')[start_recno:end_recno]
    fields = list(data_list.values()[0])

    # 创建工作簿
    xls_file = xlwt.Workbook(encoding='utf8')
    # 添加工作表，同一单元格可重复修改
    sheet = xls_file.add_sheet('Sheet1', cell_overwrite_ok=True)
    for col, title in enumerate(fields):
        sheet.write(0, col, data_list.model._meta.get_field(title).verbose_name)
    row_index = 1
    for row in data_list.values():
        print(row)
        col_index = 0
        for value in row.values():
            sheet.write(row_index, col_index, value)
            col_index += 1
        row_index += 1
    xls_file.save(response)
    return response